package com.dalin.framework.dao.imple;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.dalin.framework.common.utils.StringUtils;
import com.dalin.framework.util.LogUtil;
import com.dalin.framework.vos.ColumnBaseInfoVo;

/*****************************************************************************
 * @content: 共用抽象连接类                                                   *
 * @author : 那建林                                                          *
 * @crtDate: 2009-06-15                                                     *
 * @version: 1.0                                                            *
 ****************************************************************************/
public class AbstractConnection {
	protected LogUtil log = LogUtil.getLogger(this.getClass());

	private String driverClassName = null;
	private String url = null;
	private String userName = null;
	private String userPswd = null;
	
	private Connection conn = null;
	protected Statement stmt = null;
	private ResultSet rs = null;
	/*************************************************************************
	 * @content: 构造函数
	 */
	protected AbstractConnection(String driverClassName, 
			String url, String userName, String userPswd) {
		this.driverClassName = driverClassName;
		this.url = url;
		this.userName = userName;
		this.userPswd = userPswd;
	}
	

	/*************************************************************************
	 * @content: 打开一个数据库连接, 
	 * @return : 得到连接实例
	 */
	protected Connection openConnection() throws Exception {
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("驱动包名有错");
		}
		try {
			conn = DriverManager.getConnection(url, userName, userPswd);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(url+"不能创建数据库的连接");
		}
		stmt = conn.createStatement();
		return conn;
	}

	
	/*************************************************************************
	 * @content: 数据检索
	 * @param  : sql查询语句
	 * @return : 结果集
	 * @throws Exception 
	 */
	protected List<Map> queryData(String sql) throws Exception {
		openConnection();
		List<Map> rtnList = new ArrayList<Map>();
		Map<String, String> map = null;
		try {
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap<String, String>();
				for (int i = 1; i < columnCount + 1; i++) {
					map.put(rsmd.getColumnName(i), 
							StringUtils.toString(rs.getString(i)));
				}
				rtnList.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return rtnList;
	}

	
	/*************************************************************************
	 * @content: 数据更新(增、删、改)
	 * @param  : sql语句
	 * @return : 影响行数
	 * @throws Exception 
	 */
	protected int executeUpdate(String sql) throws Exception {
		//StringUtils.printInfo(this.getClass(), "sql", sql);
		openConnection();
		int i = -1;
		try {
			i = stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			closeConnection();
		}
		return i;
	}

	
	/*************************************************************************
	 * @content: 关闭数据库连接
	 */
	protected void closeConnection() {
		try {
			if (rs != null) rs.close();
			if (stmt != null)stmt.close();
			if (conn != null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/*************************************************************************
	 * @content: 根据表名来动态查询字段结构信息，放入List中
	 * @param  : 表名
	 * @return : List集合
	 * @throws Exception 
	 */
	protected Set<ColumnBaseInfoVo> queryColumnInfosByTableName(String tableName) throws Exception {
		Set<ColumnBaseInfoVo> rtnSet = new HashSet<ColumnBaseInfoVo>();
		ColumnBaseInfoVo columnInfoVo = null;
		try {
			openConnection();
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet colRet = meta.getColumns(null,userName.toUpperCase(), tableName, "%");
		    while(colRet.next()) {
		    	columnInfoVo = new ColumnBaseInfoVo();
		    	columnInfoVo.setColumnComment(colRet.getString(13));
		    	columnInfoVo.setColumnName(colRet.getString("COLUMN_NAME").toUpperCase());
		    	columnInfoVo.setColumnType(colRet.getString("TYPE_NAME"));
		    	columnInfoVo.setColumnLength(colRet.getInt("COLUMN_SIZE"));
		    	columnInfoVo.setNullable(colRet.getInt("NULLABLE"));
		    	columnInfoVo.setDecimalDigits(colRet.getInt("DECIMAL_DIGITS"));
		    	ResultSet kerRes = meta.getPrimaryKeys(null,userName.toUpperCase(), tableName);
			    while(kerRes.next()) {
			    	if (columnInfoVo.getColumnName().equals(kerRes.getString(4))) {
			    		columnInfoVo.setPrimaryKey(true);
			    	}
			    }
			    kerRes.close();
		    	rtnSet.add(columnInfoVo);
		    }
		    colRet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return rtnSet;
	}


	public ColumnBaseInfoVo findColumnInfoByColumnName(String tableName,
			String columnName) throws Exception {
		ColumnBaseInfoVo columnInfoVo = null;
		try {
			openConnection();
			DatabaseMetaData meta = conn.getMetaData();
			ResultSet colRet = meta.getColumns(null,"%", tableName, columnName);
		    while(colRet.next()) {
		    	columnInfoVo = new ColumnBaseInfoVo();
		    	columnInfoVo.setColumnName(colRet.getString("COLUMN_NAME"));
		    	columnInfoVo.setColumnType(colRet.getString("TYPE_NAME"));
		    	columnInfoVo.setColumnLength(colRet.getInt("COLUMN_SIZE"));
		    	columnInfoVo.setNullable(colRet.getInt("NULLABLE"));
		    	columnInfoVo.setDecimalDigits(colRet.getInt("DECIMAL_DIGITS"));
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return columnInfoVo;
	}
}
